package db.document;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import o.销售单据类型;
import db.Database;
import db.history.SaleLogDB;
import db.history.SystemLogDB;
import db.history.经营情况DB;
import po.AccountBookPo;
import po.DocumentPo;
import po.Po;
import po.SystemLogPo;
import po.商品列表单项po;
import po.库存赠送单po;
import po.销售明细po;
import po.销退售单po;
import tool.LongStringTransformer;

public class SalesAndReturnDocumentDB extends DocumentDB{
 
	
	public SalesAndReturnDocumentDB(AccountBookPo ab) throws SQLException {
		super(ab);
		String sales_item="CREATE TABLE IF NOT EXISTS `seac2`.`"+ab.getName()+"_sale_return_item` (`f_key` int(4) NOT NULL, `f_commodityID` varchar(20) NOT NULL COMMENT '商品编号', `f_commodityName` varchar(20) NOT NULL COMMENT '商品名称', `f_commodityModel` varchar(20) NOT NULL COMMENT '商品型号', `f_commodityNum` int(4) NOT NULL COMMENT '数量', `f_commodityUnitPrice` bigint(8) NOT NULL COMMENT '单价', `f_total` bigint(8) NOT NULL COMMENT '总价', `f_remark` varchar(50) NOT NULL COMMENT '备注', `f_id` int(4) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`f_id`),UNIQUE KEY `f_id` (`f_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8";
		String createT_sale_return="CREATE TABLE IF NOT EXISTS `seac2`.`"+ab.getName()+"_sale_return` ( `f_id` varchar(30) NOT NULL, `f_type` int(1) NOT NULL DEFAULT '0', `f_client` varchar(20) NOT NULL, `f_yewuyuan` varchar(20) NOT NULL, `f_operator` varchar(20) NOT NULL, `f_storage` varchar(20) NOT NULL, `f_item` int(8) NOT NULL AUTO_INCREMENT, `f_total_before` bigint(20) NOT NULL, `f_discount` bigint(20) NOT NULL, `f_coupons` bigint(20) NOT NULL, `f_total_after` bigint(20) NOT NULL, `f_remark` varchar(100) NOT NULL, `f_isApproved` int(1) NOT NULL DEFAULT '0',`f_date` date NOT NULL,PRIMARY KEY (`f_item`),UNIQUE KEY `f_item` (`f_item`)) ENGINE=InnoDB DEFAULT CHARSET=utf8";
		Connection c = Database.getConnection();
		Statement s = c.createStatement();
		s.execute(createT_sale_return);
		s.execute(sales_item);
		s.close();
		
	}

	@Override
	public Po add(DocumentPo po) {
		List<商品列表单项po> list=((销退售单po)po).get出货商品清单();
		String r_id;int type;int client_level=0;long strategy_discount=0;long top = 0,yingshou=0;//应收额度
		String client=((销退售单po) po).get客户();
		String yewuyuan=((销退售单po)po).get业务员();
		String operator=((销退售单po) po).get操作员();
		String storage=((销退售单po) po).get仓库();
		long total_before=((销退售单po) po).get折让前总额();
		long discount=((销退售单po) po).get折让();
		long coupons=((销退售单po) po).get使用代金券金额();
		long total_after=((销退售单po) po).get折让后总额();
		String remark=((销退售单po) po).get备注();
		String date=new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		int total_num=0;
		Po m=new Po();
		if(client==null||yewuyuan==null||storage==null){
			m.failed("请完整单据！");
			return m;
		}
		Connection c=Database.getConnection();
		Statement s,s1;
		try {
			int key=0;
			s = c.createStatement();
			ResultSet rs=s.executeQuery("select * from "+book.getName()+"_sale_return");
			while(rs.next()){
				total_num++;
			}
			if(((销退售单po) po).get类型()==销售单据类型.销售单){
				//判断商品数量是否超过库存值
				int stock=0;
				for(int i=0;i<list.size();i++){
					ResultSet rs5=s.executeQuery("select f_stockNum from "+book.getName()+"_commodity where f_commodityID='"+list.get(i).get商品编号()+"'");
					while(rs5.next()){
						stock=rs5.getInt(1);
					}
					if(list.get(i).get数量()>stock){
						m.failed(list.get(i).get商品名称()+"数量大于库存数量！");
						return m;
					}
				}
				
				//首先判断客户是否超过应收额度，如果是，则添加失败
				ResultSet r=s.executeQuery("select yingshouedu,yingshou from "+book.getName()+"_client where name='"+client+"'");
				while(r.next()){
					top=r.getLong(1);
					yingshou=r.getLong(2);
				}
				if((yingshou+total_after)>top){
					m.failed("该客户达到应收额度，故销售单添加失败！");
					return m;
				}
				type=0;
				r_id=getDocumentId("XSD",total_num);
				ResultSet rs1=s.executeQuery("select class from "+book.getName()+"_client where name='"+client+"'");
				while(rs1.next()){
					client_level=rs1.getInt(1);
				}
				s1=c.createStatement();
				ResultSet rs3=s1.executeQuery("select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='seac2' and `TABLE_NAME`='"+book.getName()+"_strategy_discount' ;");
				while(rs3.next()){
				ResultSet rs2=s.executeQuery(" select f_discount  from  "+book.getName()+"_strategy_discount where f_from<='"+date+"' and f_to>='"+date+"' and f_client_level='"+client_level+"' and f_totalRequire<='"+total_after+"' order by f_totalRequire desc limit 1");
				while(rs2.next()){
					strategy_discount=rs2.getLong(1);
					discount+=strategy_discount;
					total_after-=strategy_discount;
					remark+="(折扣已包含促销折扣！)";
				}
				}
				s1.close();
				}else{
				type=1;
				r_id=getDocumentId("XSTHD",total_num);
			}
			
			s.executeUpdate("insert into "+book.getName()+"_sale_return(f_id,f_type,f_client,f_yewuyuan,f_operator,f_storage,f_total_before,f_discount,f_coupons,f_total_after,f_remark,f_date) values('"+r_id+"','"+type+"','"+client+"','"+yewuyuan+"','"+operator+"','"+storage+"','"+total_before+"','"+discount+"','"+coupons+"','"+total_after+"','"+remark+"','"+date+"');");
			ResultSet r=s.executeQuery("select f_item from "+book.getName()+"_sale_return where f_id='"+r_id+"'");
			while(r.next()){
		    key=r.getInt("f_item");
			}
			for(int i=0;i<list.size();i++){
		    String re=list.get(i).get备注()==null? "":list.get(i).get备注();
			s.executeUpdate("insert into "+book.getName()+"_sale_return_item(f_key,f_commodityID,f_commodityName,f_commodityModel,f_commodityNum,f_commodityUnitPrice,f_total,f_remark) values('"+key+"','"+list.get(i).get商品编号()+"','"+list.get(i).get商品名称()+"','"+list.get(i).get商品型号()+"','"+list.get(i).get数量()+"','"+list.get(i).get单价()+"','"+list.get(i).get金额()+"','"+re+"');");
			}
			s.close();
			m.successed();
			SystemLogDB log=new SystemLogDB();
			SystemLogDB.iniSystemLogDB();
			log.add(new SystemLogPo(po.get操作员(),"add",r_id));
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
	}

	@Override
	public Po update(DocumentPo po) {
		String doc_id=((销退售单po) po).get单据编号();int key=0;
		List<商品列表单项po> list=((销退售单po)po).get出货商品清单();
		String client=((销退售单po) po).get客户();
		String yewuyuan=((销退售单po)po).get业务员();
		String operator=((销退售单po) po).get操作员();
		String storage=((销退售单po) po).get仓库();
		long discount=((销退售单po) po).get折让();
		long coupons=((销退售单po) po).get使用代金券金额();
		String remark=((销退售单po) po).get备注();
		Po m=new Po();
		Connection c=Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();
			if(client!=null){
				s.addBatch("update "+book.getName()+"_sale_return set f_client='"+client+"' where f_id='"+doc_id+"'");
			}
			if(yewuyuan!=null){
				s.addBatch("update "+book.getName()+"_sale_return set f_yewuyuan='"+yewuyuan+"' where f_id='"+doc_id+"'");
			}
			if(operator!=null){
				s.addBatch("update "+book.getName()+"_sale_return set f_operator='"+operator+"' where f_id='"+doc_id+"'");
			}
			if(storage!=null){
				s.addBatch("update "+book.getName()+"_sale_return set f_storage='"+storage+"' where f_id='"+doc_id+"'");
			}
			s.addBatch("update "+book.getName()+"_sale_return set f_discount='"+discount+"' where f_id='"+doc_id+"'");
			s.addBatch("update "+book.getName()+"_sale_return set f_coupons='"+coupons+"' where f_id='"+doc_id+"'");
			if(remark!=null){
				s.addBatch("update "+book.getName()+"_sale_return set f_remark='"+remark+"' where f_id='"+doc_id+"'");
			}		
			ResultSet r=s.executeQuery("select f_item from "+book.getName()+"_sale_return where f_id='"+doc_id+"'");
			while(r.next()){
		    key=r.getInt("f_item");
			}
			s.addBatch("delete from "+book.getName()+"_sale_return_item where f_key='"+key+"'");
			for(int i=0;i<list.size();i++){
				s.addBatch("insert into "+book.getName()+"_sale_return_item(f_key,f_commodityID,f_commodityName,f_commodityModel,f_commodityNum,f_commodityUnitPrice,f_total,f_remark) values('"+key+"','"+list.get(i).get商品编号()+"','"+list.get(i).get商品名称()+"','"+list.get(i).get商品型号()+"','"+list.get(i).get数量()+"','"+list.get(i).get单价()+"','"+list.get(i).get金额()+"','"+list.get(i).get备注()+"');");
			}
		    s.executeBatch();
			s.close();
			m.successed();
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
	}

	@Override
	public List<DocumentPo> getUnapproval() {
		List<DocumentPo> list=new ArrayList<DocumentPo>();
		Connection c=Database.getConnection();
		Statement s;Statement s2;
		try {
			s = c.createStatement();
			ResultSet rs=s.executeQuery("select * from "+book.getName()+"_sale_return where f_isApproved='"+0+"'");
			while(rs.next()){
				销退售单po po=new 销退售单po();
				po.set单据编号(rs.getString("f_id"));
				po.set类型(rs.getInt("f_type")==0? 销售单据类型.销售单:销售单据类型.销售退货单);
				po.set客户(rs.getString("f_client"));
				po.set业务员(rs.getString("f_yewuyuan"));
				po.set仓库(rs.getString("f_storage"));
				po.set操作员(rs.getString("f_operator"));
				po.set折让前总额(rs.getLong("f_total_before"));
				po.set折让(rs.getLong("f_discount"));
				po.set使用代金券金额(rs.getLong("f_coupons"));
				po.set折让后总额(rs.getLong("f_total_after"));
				po.set备注(rs.getString("f_remark"));
			
				//po.setDate(date2);
				List<商品列表单项po> item=new ArrayList<商品列表单项po>();
				int key=rs.getInt("f_item");
				s2=c.createStatement();
				ResultSet rs2=s2.executeQuery("select * from "+book.getName()+"_sale_return_item where f_key='"+key+"'");
				while(rs2.next()){
					商品列表单项po _item=new 商品列表单项po();
					_item.set商品编号(rs2.getString("f_commodityID"));
					_item.set商品名称(rs2.getString("f_commodityName"));
					_item.set商品型号(rs2.getString("f_commodityModel"));
					_item.set数量(rs2.getInt("f_commodityNum"));
					_item.set单价(rs2.getLong("f_commodityUnitPrice"));
					_item.set金额(rs2.getLong("f_total"));
					_item.set备注(rs2.getString("f_remark"));
					item.add(_item);
					}
				s2.close();
				po.set出货商品清单(item);
				list.add(po);
		}
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<DocumentPo> get(DocumentPo from, DocumentPo to) {
		//时间，客户，仓库
				List<DocumentPo> list=new ArrayList<DocumentPo>();
				//String operator=from.get操作员();
				String storage=from.get仓库();
				String client=from.get客户();
				String yewuyuan=from.get业务员();
				String from_date;String to_date;
				from_date=from.getDate()==null? null:new SimpleDateFormat("yyyy-MM-dd").format(from.getDate());
				to_date=to.getDate()==null?null:new SimpleDateFormat("yyyy-MM-dd").format(to.getDate());
				String query_client=client==null? "":"and f_client like '%"+client+"%'";
				String query_storage=storage==null? "":"and f_storage like '%"+storage+"%'";
				String query_yewuyuan=yewuyuan==null? "":"and f_yewuyuan like '%"+yewuyuan+"%'";
				String query_from=from_date==null? "":"and f_date>='"+from_date+"'";
				String query_to=to_date==null? "":"and f_date<='"+to_date+"'";
			   
				int type=from.isType()? 1:0;             //销售单传递type=false
				Connection c=Database.getConnection();
				Statement s;Statement s2;ResultSet rs = null;
				try {
					s = c.createStatement();
					rs=s.executeQuery("select * from "+book.getName()+"_sale_return where f_isApproved='"+1+"'"+query_yewuyuan+query_client+query_storage+query_from+query_to+ "and f_type='"+type+"'");
					while(rs.next()){
						销退售单po po=new 销退售单po();
						po.set单据编号(rs.getString("f_id"));
						po.set类型(rs.getInt("f_type")==0? 销售单据类型.销售单:销售单据类型.销售退货单);
						po.set客户(rs.getString("f_client"));
						po.set业务员(rs.getString("f_yewuyuan"));
						po.set仓库(rs.getString("f_storage"));
						po.set操作员(rs.getString("f_operator"));
						po.set折让前总额(rs.getLong("f_total_before"));
						po.set折让(rs.getLong("f_discount"));
						po.set使用代金券金额(rs.getLong("f_coupons"));
						po.set折让后总额(rs.getLong("f_total_after"));
						po.set备注(rs.getString("f_remark"));
					   // po.setDate(rs.getDate("f_date"));
						
						List<商品列表单项po> item=new ArrayList<商品列表单项po>();
						int key=rs.getInt("f_item");
						s2=c.createStatement();
						ResultSet rs2=s2.executeQuery("select * from "+book.getName()+"_sale_return_item where f_key='"+key+"'");
						while(rs2.next()){
							商品列表单项po _item=new 商品列表单项po();
							_item.set商品编号(rs2.getString("f_commodityID"));
							_item.set商品名称(rs2.getString("f_commodityName"));
							_item.set商品型号(rs2.getString("f_commodityModel"));
							_item.set数量(rs2.getInt("f_commodityNum"));
							_item.set单价(rs2.getLong("f_commodityUnitPrice"));
							_item.set金额(rs2.getLong("f_total"));
							_item.set备注(rs2.getString("f_remark"));
							item.add(_item);
							}
						s2.close();
						po.set出货商品清单(item);
						list.add(po);
				}
					s.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				return list;
			}
	
	public Po approve(DocumentPo document) {
		String id=((销退售单po)document).get单据编号();
		String date = null;
		Po m=new Po();int key = 0;
		List<String>commodityID=new ArrayList<String>();
		List<String>commodityName=new ArrayList<String>();
		List<String>commodityModel=new ArrayList<String>();
		List<Integer>commodityNum=new ArrayList<Integer>();
		List<Long> commodityTotalPrice=new ArrayList<Long>();
		List<Long>commodityPrice=new ArrayList<Long>();
	    long total_after = 0;String client=null;int client_level=0;long discount=0;
	    String storage=null;String yewuyuan="";
		Connection c=Database.getConnection();
		Statement s,s1;
		try {
			s = c.createStatement();
			ResultSet rs7=s.executeQuery("select f_isApproved from "+book.getName()+"_sale_return where f_id='"+id+"'");
			while(rs7.next()){
				if(rs7.getInt(1)==1){
					m.failed("该单据已被审批！");
					return m;
				}
			}
			s.executeUpdate("update "+book.getName()+"_sale_return set f_isApproved='"+1+"'where f_id='"+id+"';");
			
			//销退售单审批后，修改客户应收应付，更改库存
			ResultSet rs=s.executeQuery("select f_item,f_total_after,f_client,f_discount,f_date,f_storage,f_yewuyuan from "+book.getName()+"_sale_return where f_id='"+id+"'");
			while(rs.next()){
				key=rs.getInt("f_item");
				total_after=rs.getLong("f_total_after");
				discount=rs.getLong("f_discount");
				client=rs.getString("f_client");
				date=new SimpleDateFormat("yyyy-MM-dd").format(rs.getDate("f_date"));
				storage=rs.getString("f_storage");
				yewuyuan=rs.getString("f_yewuyuan");
			}
		    ResultSet rs2=s.executeQuery("select class from "+book.getName()+"_client where name='"+client+"'");
		    while(rs2.next()){
		    	client_level=rs2.getInt(1);
		    }
			
			
			ResultSet rs3=s.executeQuery("select f_commodityID,f_commodityName,f_commodityModel,f_commodityNum ,f_total,f_commodityUnitPrice from "+book.getName()+"_sale_return_item where f_key='"+key+"'");
			while(rs3.next()){
				commodityID.add(rs3.getString("f_commodityID"));
				commodityName.add(rs3.getString("f_commodityName"));
				commodityModel.add(rs3.getString("f_commodityModel"));
				commodityNum.add(rs3.getInt("f_commodityNum"));
				commodityTotalPrice.add(rs3.getLong("f_total"));
				commodityPrice.add(rs3.getLong("f_commodityUnitPrice"));
				
			}
			if(id.contains("XSTHD")){
				s.execute("update "+book.getName()+"_client set yingfu=(yingfu+"+total_after+") where name='"+client+"'");
				for(int i=0;i<commodityID.size();i++){
					s.execute("update "+book.getName()+"_commodity set f_stockNum=(f_stockNum+"+commodityNum.get(i)+") where f_commodityID='"+commodityID.get(i)+"'");
					s.execute("update "+book.getName()+"_commodity set f_TotalMoney=(f_totalMoney+"+commodityTotalPrice.get(i)+") where f_commodityID='"+commodityID.get(i)+"'");
					s.execute("update "+book.getName()+"_commodity set f_stockAveragePrice=(case when f_stockNum=0 then 0 else f_totalMoney/f_stockNum end)"+" where f_commodityID='"+commodityID.get(i)+"'");
				}
			}
			else if(id.contains("XSD")){          
				//添加销售收入
				经营情况DB db=new 经营情况DB(book);
				Po r1=db.add销售收入(total_after);
				if(!r1.isSuccessed()){
					m.failed("添加商品销售收入失败！");
					return m;
				}
				Po r2=db.add折让(discount);
				if(!r2.isSuccessed()){
					m.failed("添加商品折让失败！");
					return m;
				}
				
				//增加应收
				s.execute("update "+book.getName()+"_client set yingshou=(yingshou+"+total_after+") where name='"+client+"'");
				
				
				//判断是否符合赠送单条件，是则自动建立库存赠送单
				s1=c.createStatement();
				ResultSet rs4=s1.executeQuery("select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='seac2' and `TABLE_NAME`='"+book.getName()+"_strategy_gift' ;");
				while(rs4.next()){					
				ResultSet rs1=s.executeQuery("select * from "+book.getName()+"_strategy_gift where f_client='"+client_level+"' and f_totalRequire<='"+total_after+"' and f_from<='"+date+"' and f_to>='"+date+"'");
				while(rs1.next()){
					库存赠送单po gift=new 库存赠送单po();
					gift.set操作员("系统");
					gift.set客户(client);
					gift.set商品名称(rs1.getString("f_commodityName"));
					gift.set商品编号(rs1.getString("f_commodityID"));
					gift.set商品型号(rs1.getString("f_commodityModel"));
					gift.set备注("系统自动生成");
					gift.setNum(rs1.getInt("f_giftNum"));
					StockSendingDocumentDB giftdb=new StockSendingDocumentDB(book);
					giftdb.add(gift);
				}
				}
				//是否符合代金券
				ResultSet rs8=s1.executeQuery("select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='seac2' and `TABLE_NAME`='"+book.getName()+"_strategy_coupons' ;");
				while(rs8.next()){					
				ResultSet rs9=s.executeQuery("select * from "+book.getName()+"_strategy_coupons where f_client_level='"+client_level+"' and f_totalRequire<='"+total_after+"' and f_from<='"+date+"' and f_to>='"+date+"'");
				while(rs9.next()){
					库存赠送单po gift_coupons=new 库存赠送单po();
					gift_coupons.set操作员("系统");
					gift_coupons.set客户(client);
					gift_coupons.set商品名称(LongStringTransformer.LongToString(rs9.getLong(7))+"元代金券");
					gift_coupons.set商品编号("代金券0000"+rs9.getInt(1));
					gift_coupons.set商品型号("代金券");
					gift_coupons.set备注("系统自动生成");
					gift_coupons.setNum(rs9.getInt(6));
					StockSendingDocumentDB giftdb=new StockSendingDocumentDB(book);
					giftdb.add(gift_coupons);
				}
				rs9.close();
				}
				rs8.close();
				s1.close();
				for(int i=0;i<commodityID.size();i++){
					//修改最近售价
					s.execute("update "+book.getName()+"_commodity set f_recent_salePrice='"+commodityPrice.get(i)+"' where f_commodityID='"+commodityID.get(i)+"'");
					//修改库存
					s.execute("update "+book.getName()+"_commodity set f_stockNum=(f_stockNum-"+commodityNum.get(i)+") where f_commodityID='"+commodityID.get(i)+"'");
					s.execute("update "+book.getName()+"_commodity set f_TotalMoney=(f_totalMoney-f_stockAveragePrice*"+commodityNum.get(i)+") where f_commodityID='"+commodityID.get(i)+"'");
					s.execute("update "+book.getName()+"_commodity set f_stockAveragePrice=(case when f_stockNum=0 then 0 else f_totalMoney/f_stockNum end)"+" where f_commodityID='"+commodityID.get(i)+"'");
					//添加销售明细
					SaleLogDB sale_log=new SaleLogDB(book);
					Date d=new Date();
					long a=d.getTime();
					/*String d2=new SimpleDateFormat("yyyy-MM-dd").format(new Date(a)); 
					System.out.println(d2);*/
					sale_log.add(new 销售明细po(a,commodityName.get(i),commodityModel.get(i),commodityPrice.get(i),commodityNum.get(i),commodityTotalPrice.get(i),yewuyuan,storage,client));
					
					
				}
				
			}
			s.close();
			m.successed();
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
	}



}
